﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using libDAL;
using System.Data;
using System.Windows.Forms;

namespace libInventory.BUS
{
    sealed class clsBUSInland : IDisposable
    {

        private cls_DAL _DAL;
        private DataSet dsInlandSetup;
        
        private string strTempConStrgrn = @"Data Source=193.9.200.92\SERVERARCHIE;Initial Catalog=ESB_FMSTransactions;User ID=abc;Password=1234";

        public clsBUSInland()
        {
            _DAL = new cls_DAL(strTempConStrgrn);
            dsInlandSetup = new DataSet();
        }

        private struct Queries
        {
            public string get_QueryToDates(string strPortID)
            {
                string strQuery = "select MAX(DateTimeInserted) as DateTimeInserted, portID, PostCode " +
                                 "from SalesTermSetupInLandCharges " +
                                 "where ltrim(rtrim(portID)) = '" + strPortID.Trim() + "'" +
                                 "group by portID,PostCode " +
                                 "order by PostCode asc";

                return strQuery;
            }

            public string get_QueryGetIDInland(string strPostCode, string strPortID)
            {
                return "select AreaDescription, InLandSetupChargesID  " +
                       "from SalesTermSetupInLandCharges " +
                       "where PortID = '" + strPortID + "' and PostCode = '" + strPostCode + "' and DatetimeInserted = " +
                       "(Select MAX(DatetimeInserted) from SalesTermSetupInLandCharges " +
                       "where PortID =  '" + strPortID + "' and PostCode = '" + strPostCode + "')";
            }

            public string get_QueryInland(string strVerb)
            {
                string strQuery = "SELECT InLandSetupChargesID, PortID, PostCode, AreaDescription, RateM3, MinRateM3, MinAmountM3, RateWeight, " +
                                   "MinRateWeight, MinAmountWeight, RateVolume, MinRateVolume, " +
                                   "MinAmountVolume, RateQuantity, MinRateQuantity, MinAmountQuantity, InvoicePercentage, " +
                                   "PerShipment, DateTimeInserted, DateTimeUpdated, LastModifiedBy, " +
                                   "HostNameIPAddress, OnUse, OnUseBy " +
                                   "FROM SalesTermSetupInLandCharges " + strVerb +
                                   "Order by PostCode asc";
              return strQuery;
            }

            public string get_QueryPorts
            {
                get
                {
                    return "select PortID, PortName from mfport ";
                }
            }
        }

        private struct InLandMaxDetails
        { 
            public string strAreaDescription;
            public string strInLandSetupChargesID;
       }

        private Queries Query;
        private InLandMaxDetails strcInlandDetails;
        public DataTable get_Ports()
        {
            DataTable dtPorts = new DataTable();
            try
            {
                dtPorts = _DAL.get_TableDetail(Query.get_QueryPorts);   
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " v"); 
            }

            return dtPorts; 
        }

        //public string strAreaDescription;
        //public string strInLandSetupChargesID;

        public string getset_AreaDescription
        {
            get
            {
                return strcInlandDetails.strAreaDescription;
            }
            set
            {
                strcInlandDetails.strAreaDescription = value;
            }
        }

        public string getset_InLandSetupChargesID
        {

            get
            {
                return strcInlandDetails.strInLandSetupChargesID; 
            }
            set
            {
                strcInlandDetails.strInLandSetupChargesID = value;
            }
        }

        public bool get_InlandDetails(string strPostCode, string strPortID)
        {
            bool bolStatus = false;
            try
            {
                // AreaDescription, InLandSetupChargesID 
                DataTable dt= new DataTable ();
               dt = _DAL.get_TableDetail (Query.get_QueryGetIDInland(strPostCode,strPortID));
                if (dt.Rows.Count > 0)
                {
                    getset_AreaDescription = dt.Rows[0]["AreaDescription"].ToString();
                    getset_InLandSetupChargesID = dt.Rows[0]["InLandSetupChargesID"].ToString();
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_InlandDetails"); 
            }

            return bolStatus;
        }

        public DataRow[] get_AreaByPortFromMemory(string strAreaDesc)
        { 
            DataRow[] drAreas = { };
            try
            {
                if (dsInlandSetup.Tables.Count > 0)
                {
                    drAreas = this.dsInlandSetup.Tables["InLand-0"].Select();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_AreaByPortFromMemory");
            }
            return drAreas;
        }

        public cls_DAL get_DAL
        {
            get
            {
                return _DAL;
            }
        }

        private string get_VerbMaxPortAreas(DataTable dtSource)
        {
            StringBuilder sbQuery = new StringBuilder();
            try
            {
                DataRow[] drPorts = dtSource.Select();
                string strAbsorber = string.Empty;
                for (int intRows = 0; intRows < drPorts.Length; intRows++)
                {
                    if (intRows == 0)
                    {strAbsorber = " WHERE ";}
                    else{strAbsorber = " OR ";}


                    for (int intCol = 0; intCol < dtSource.Columns.Count; intCol++)
                    {
                        if (intCol == 0)
                        {
                            strAbsorber += dtSource.Columns[intCol].ColumnName + " = '"  +
                            Convert.ToDateTime(drPorts[intRows][dtSource.Columns[intCol].ColumnName]).ToString("MM/dd/yyyy HH:mm:ss.fff") + "'";
                        }
                        else
                        {
                            strAbsorber += dtSource.Columns[intCol].ColumnName + " = '" + drPorts[intRows][dtSource.Columns[intCol].ColumnName] + "'";
                        }

                        if (intCol < dtSource.Columns.Count - 1)
                        {
                            strAbsorber += " and ";
                        }
                    }
                    
                    sbQuery.AppendLine(strAbsorber);
                    strAbsorber = string.Empty;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_VerbMaxPortAreas"); 
            }
            return sbQuery.ToString();
        }

        public DataRow[] get_AreasByPortFromDB(string strPortID)
        {
            DataRow[] drAreas = { };
            try
            {
                dsInlandSetup = new DataSet();
                DataTable dtMaxDates = new DataTable();
                dtMaxDates = _DAL.get_TableDetail(Query.get_QueryToDates(strPortID));

                if (_DAL.set_DataWCardinality_ds(Query.get_QueryInland(get_VerbMaxPortAreas(dtMaxDates)),
                    ref dsInlandSetup, "InLand-0") == true)
                {
                    if (dtMaxDates.Rows.Count > 0)
                    {
                        drAreas = dsInlandSetup.Tables["InLand-0"].Select();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_AreasByPort"); 
            }

            return drAreas;
        }

        public bool set_UpdateInsertToDataSet(DataTable dtStorage)
        {
            bool bolStatus = false;
            try
            {
                DataTable dtCheck = dsInlandSetup.Tables["InLand-0"].Clone ();
                DataRow[] drCheck = { };
                DataRow[] drHolder = dtStorage.Select();
                DataRow drInsert;
                for (int intRow = 0; intRow < drHolder.Length; intRow++)
                {
                    drCheck = dsInlandSetup.Tables["InLand-0"].Select("InLandSetupChargesID = '" + drHolder[intRow]["InLandSetupChargesID"] + "'");
                    if (drCheck.Length > 0)
                    {
                        for (int intCol = 0; intCol < dtStorage.Columns.Count; intCol++)
                        {
                            drCheck[0][intCol] = drHolder[intRow][intCol];
                        }
                        drCheck[0]["DateTimeUpdated"] = drCheck[0]["DateTimeInserted"];
                        drCheck[0]["HostNameIPAddress"] = clsGenFunction.getHostIP();
                        drCheck[0]["DateTimeInserted"] = clsGenFunction.get_ServerDateTimeRAW(_DAL);  
                    }
                    else
                    {
                        drInsert = dsInlandSetup.Tables["InLand-0"].NewRow();
                        for (int intCol = 0; intCol < dtStorage.Columns.Count; intCol++)
                        {
                            drInsert[dtStorage.Columns[intCol].ColumnName] = drHolder[intRow][dtStorage.Columns[intCol].ColumnName];
                        }
                        drInsert["DateTimeInserted"] = clsGenFunction.get_ServerDateTimeRAW(_DAL);  
                        drInsert["DateTimeUpdated"] = clsGenFunction.get_ServerDateTimeRAW(_DAL);
                        drInsert["HostNameIPAddress"] = clsGenFunction.getHostIP();
                        dsInlandSetup.Tables["InLand-0"].Rows.Add(drInsert);
                    }
                }
                if (get_CheckSaved() == true)
                {
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_UpdateInsertToDataSet"); 
            }
            return bolStatus;
        }

        private bool get_CheckSaved()
        {
            bool bolStatus = false;
            try
            {
                bolStatus = _DAL.Update_batch_ds(ref dsInlandSetup, "InLand-0");  
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_CheckSaved"); 
            }
            return bolStatus;
        }

        public void Dispose()
        {
            GC.Collect();
        }
    }
}
